package com.jlnk.androidfmc.database;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

import com.jlnk.androidfmc.entity.Product;

import java.util.ArrayList;
import java.util.List;

public class ProductDatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "productDatabase";
    private static final int DATABASE_VERSION = 1;

    // Table for categories
    public static final String TABLE_CATEGORIES = "categories";
    private static final String COLUMN_CATEGORY_ID = "id";
    private static final String COLUMN_CATEGORY_NAME = "name";

    private static final String CREATE_TABLE_CATEGORIES = "CREATE TABLE "
            + TABLE_CATEGORIES + "("
            + COLUMN_CATEGORY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + COLUMN_CATEGORY_NAME + " TEXT NOT NULL UNIQUE "
            + ")";

    // Table for products
    public static final String TABLE_PRODUCTS = "products";
    public static final String COLUMN_PRODUCT_ID = "id";
    public static final String COLUMN_PRODUCT_NAME = "name";
    public static final String COLUMN_PRODUCT_DESCRIPTION = "description";
    public static final String COLUMN_PRODUCT_IMAGE_PATH = "image_path";
    public static final String COLUMN_PRODUCT_CATEGORY_ID = "category_id"; // Foreign key
    public static final String COLUMN_PRODUCT_PRICE = "price";

    public static final class Category {
        public static final int FRUITS_VEGETABLES = 1;  // 水果和蔬菜
        public static final int GRAINS_OIL = 2;         // 米面粮油
        public static final int FARM_SUPPLIES = 3;      // 农资农具
    }

    private static final String CREATE_TABLE_PRODUCTS = "CREATE TABLE "
            + TABLE_PRODUCTS + "("
            + COLUMN_PRODUCT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"  // 假设使用自增整数作为主键
            + COLUMN_PRODUCT_NAME + " TEXT,"
            + COLUMN_PRODUCT_DESCRIPTION + " TEXT,"
            + COLUMN_PRODUCT_IMAGE_PATH + " TEXT,"
            + COLUMN_PRODUCT_CATEGORY_ID + " INTEGER,"
            + COLUMN_PRODUCT_PRICE + " REAL,"
            + " FOREIGN KEY(" + COLUMN_PRODUCT_CATEGORY_ID + ") REFERENCES " + TABLE_CATEGORIES + "(" + COLUMN_CATEGORY_ID + ")"
            + ")";

    public ProductDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_CATEGORIES);
        db.execSQL(CREATE_TABLE_PRODUCTS);
        // 插入初始分类数据
        insertInitialCategories(db);
    }

    private void insertInitialCategories(SQLiteDatabase db) {
        String insertCategory = "INSERT INTO " + TABLE_CATEGORIES + " (" + COLUMN_CATEGORY_NAME + ") VALUES (?)";
        SQLiteStatement statement = db.compileStatement(insertCategory);

        try {
            // 注意这里的顺序，这将决定分类在数据库中的存储顺序
            statement.bindString(1, "水果和蔬菜");
            if (statement.executeInsert() > 0) {
                Log.i("Database", "Inserted 水果和蔬菜 category");
            }

            statement.bindString(1, "米面粮油");
            if (statement.executeInsert() > 0) {
                Log.i("Database", "Inserted 米面粮油 category");
            }

            statement.bindString(1, "农资农具");
            if (statement.executeInsert() > 0) {
                Log.i("Database", "Inserted 农资农具 category");
            }
        } finally {
            statement.close();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w("Database", "Upgrading database from version " + oldVersion + " to " + newVersion);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCTS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORIES);
        onCreate(db);
    }

    public List<Product> getProductsByCategory(int categoryId) {
        SQLiteDatabase db = this.getReadableDatabase();
        String sql = "SELECT * FROM " + TABLE_PRODUCTS + " WHERE " + COLUMN_PRODUCT_CATEGORY_ID + " = ?";
        Cursor cursor = db.rawQuery(sql, new String[]{String.valueOf(categoryId)});

        List<Product> productList = new ArrayList<>();
        try {
            while (cursor.moveToNext()) {
                int id = cursor.getInt(cursor.getColumnIndex(COLUMN_PRODUCT_ID));
                String name = cursor.getString(cursor.getColumnIndex(COLUMN_PRODUCT_NAME));
                String description = cursor.getString(cursor.getColumnIndex(COLUMN_PRODUCT_DESCRIPTION));
                String imagePath = cursor.getString(cursor.getColumnIndex(COLUMN_PRODUCT_IMAGE_PATH));
                int categoryIdFromDb = cursor.getInt(cursor.getColumnIndex(COLUMN_PRODUCT_CATEGORY_ID));
                double price = cursor.getDouble(cursor.getColumnIndex(COLUMN_PRODUCT_PRICE));

                Product product = new Product(String.valueOf(id), name, description, imagePath, categoryIdFromDb, price);
                productList.add(product);
            }
        } finally {
            cursor.close();
            db.close();
        }
        return productList;
    }

    public List<String> getAllCategoriesNames() {
        List<String> categoriesNames = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT " + COLUMN_CATEGORY_NAME + " FROM " + TABLE_CATEGORIES, null);

        try {
            if (cursor.moveToFirst()) {
                do {
                    String categoryName = cursor.getString(0); // 假设 CATEGORY_NAME 列的索引为0
                    categoriesNames.add(categoryName);
                } while (cursor.moveToNext());
            }
        } finally {
            if (cursor != null) {
                cursor.close(); // 关闭游标
            }
            db.close(); // 关闭数据库
        }
        return categoriesNames;
    }
}